﻿Imports System.Data.OleDb
Imports System.IO
Imports System.Data.SqlClient

Partial Public Class admin_estudiante
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        PanelFormulario.Visible = False
        PanelArchivo.Visible = True
        Form.Visible = True
        File.Visible = False

        lblForm.Visible = False
        LblArchivo.Visible = True
    End Sub

    Protected Sub btnInsertar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnInsertar.Click
        Try
            Dim cl As New ASI_2UFG_BL.class_admin
            cl.Insertar_Estudiante("I", CARNET.Text, Apellidos.Text, Nombres.Text, Email.Text, Usuario.Text, Clave.Text, Estado.SelectedValue, ROL.SelectedValue, 0)
            If cl.log = 0 Then
                Me.lbMensaje.Text = "Ingresado Con Exito"
                GridEstudiante.DataBind()
            Else
                Me.lbMensaje.Text = "Error al Ingresar el Registro"
            End If
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Critical)
        End Try
    End Sub

    Protected Sub btonModificar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btonModificar.Click
        Try
            Dim cl As New ASI_2UFG_BL.class_admin
            cl.Modificar_Estudiante("M", txtID.Text, CARNET.Text, Apellidos.Text, Nombres.Text, Email.Text, Usuario.Text, Clave.Text, Estado.SelectedValue, ROL.SelectedValue, 0)
            If cl.log = 0 Then
                Me.lbMensaje.Text = "Modificado Con Exito"
                GridEstudiante.DataBind()
            Else
                Me.lbMensaje.Text = "Error al Modificar el Registro"
            End If
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Critical)
        End Try
    End Sub

    Protected Sub btonEliminar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btonEliminar.Click
        Try
            Dim cl As New ASI_2UFG_BL.class_admin
            cl.Eliminar_Estudiante("E", txtID.Text, 0)
            If cl.log = 0 Then
                Me.lbMensaje.Text = "Eliminado Con Exito"
                GridEstudiante.DataBind()
            Else
                Me.lbMensaje.Text = "Error al Eliminar el Registro"
            End If
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Critical)
        End Try
    End Sub

    Protected Sub Form_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Form.Click
        PanelFormulario.Visible = True
        Form.Visible = False
        PanelArchivo.Visible = False
        File.Visible = True

        lblForm.Visible = True
        LblArchivo.Visible = False
    End Sub

    Protected Sub File_Click(ByVal sender As Object, ByVal e As EventArgs) Handles File.Click
        PanelFormulario.Visible = False
        Form.Visible = True
        PanelArchivo.Visible = True
        File.Visible = False

        lblForm.Visible = False
        LblArchivo.Visible = True
    End Sub
    Private Sub GetExcelSheets(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)

        Dim conStr As String = ""

        Select Case Extension

            Case ".xls"

                'Excel 97-03

                'conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString()
                conStr = "Provider=Microsoft.Jet.OLEDB.4.0;				 Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

                Exit Select

            Case ".xlsx"

                'Excel 07

                'conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString()
                conStr = "Provider=Microsoft.ACE.OLEDB.12.0;			  Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

                Exit Select

        End Select



        'Get the Sheets in Excel WorkBoo

        conStr = String.Format(conStr, FilePath, isHDR)

        Dim connExcel As New OleDbConnection(conStr)

        Dim cmdExcel As New OleDbCommand()

        Dim oda As New OleDbDataAdapter()

        cmdExcel.Connection = connExcel

        connExcel.Open()



        'Bind the Sheets to DropDownList

        dropHoja.Items.Clear()

        dropHoja.Items.Add(New ListItem("--Seleccionar Hoja--", ""))

        dropHoja.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

        dropHoja.DataTextField = "TABLE_NAME"

        dropHoja.DataValueField = "TABLE_NAME"

        dropHoja.DataBind()

        connExcel.Close()


        LblArchivo.Text = Path.GetFileName(FilePath)

        BtonUp.Visible = False


    End Sub


    Protected Sub BtonUp_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtonUp.Click
        If FileUp1.HasFile Then

            Dim Nombre As String = Path.GetFileName(FileUp1.PostedFile.FileName)
            Dim Extension As String = Path.GetExtension(FileUp1.PostedFile.FileName)
            'Dim FolderPath As String = ConfigurationManager.AppSettings("~\ExcelDoc\")
            Dim FolderPath As String = Server.MapPath("~\ExcelDoc\")
            Dim FilePath As String = FolderPath + Nombre

            'File.Delete(Server.MapPath(FullDir))

            FileUp1.SaveAs(FilePath)

            GetExcelSheets(FilePath, Extension, "YES")

        End If
    End Sub

    Protected Sub BtonCargar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtonCargar.Click
        Dim FileName As String = LblArchivo.Text
        Dim Extension As String = Path.GetExtension(FileName)
        Dim FolderPath As String = Server.MapPath("~\ExcelDoc\")
        Dim FullDir As String = FolderPath + FileName
        Dim hoja As String = dropHoja.SelectedItem.Text
        Dim tabla As String = "estudiante"


        Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FullDir + "; Extended Properties=""Excel 12.0 Xml; HDR=Yes""")
        ExcelConnection.Open()

        Dim expr As String = "SELECT * FROM [" + hoja + "]"
        Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDbDataReader

        Dim SQLconn As New SqlConnection()
        Dim ConnString As String = "Data Source=EMANUEL-HP; Initial Catalog=ASI2UFG; User Id=sa; Password=1234;"
        SQLconn.ConnectionString = ConnString
        SQLconn.Open()

        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
            bulkCopy.DestinationTableName = tabla

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()

                lblMensaje.ForeColor = Drawing.Color.Green
                LblArchivo.Text = "INGRESADO CON EXITO "
                GridEstudiante.DataBind()



            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using

        'File.Delete(Server.MapPath(FullDir))
    End Sub
End Class